--------------------------------------------------------------------------------
-- @name: different plans
-- @author: dion cho
-- @note: test case for the the various reasons for different plans on the same SQL
--------------------------------------------------------------------------------

set pages 100
set trimspool on
set timing off
set echo off

@capture_on

select * from v$version where rownum = 1;

desc v$sql_shared_cursor

--ed shared_cursor
drop table t1 purge;

create table t1(c1 int, c2 varchar2(10));

insert into t1 
select 1, level from dual connect by level <= 10000;

insert into t1
select level, level from dual connect by level <= 10000;

create index t1_n1 on t1(c1);

exec dbms_stats.gather_table_stats(user, 't1', -
		method_opt=>'for columns c1 size skewonly');

@tab_stat t1

-- bind mismatch due to varchar2 length
alter system flush shared_pool;


alter session set events '10503 trace name context forever, level 2000';

var b1 number;
exec :b1 := 1;
var b2 varchar2(10);
exec :b2 := '1';

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;

@stat

var b1 number;
exec :b1 := 2;
var b2 varchar2(1000);
exec :b2 := '1';

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;

@stat

col sql_id new_value sql_id

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;

select prev_sql_id as sql_id from v$session where sid = userenv('sid');

set line 120
select * from table(dbms_xplan.display_cursor('&sql_id', null, 'typical'));

@shared_cursor2 &sql_id

-- another execution plan by bind mismatch
var b1 number;
exec :b1 := 1;
var b2 varchar2(500);
exec :b2 := '1';

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;

@stat

@shared_cursor2 &sql_id

-- another child cursor but same plan!
var b1 number;
exec :b1 := 1;
var b2 varchar2(4000);
exec :b2 := '1';

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;

@stat

@shared_cursor2 &sql_id

set line 120
select * from table(dbms_xplan.display_cursor('&sql_id', null, 'typical'));

-- bind mismatch due to the type conversion
alter system flush shared_pool;

var b1 number;
exec :b1 := 1;
var b2 varchar2(10);
exec :b2 := '1';

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;

@stat

var b1 number;
exec :b1 := 2;
var b2 number;
exec :b2 := 1;

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;

@stat

set line 120
select * from table(dbms_xplan.display_cursor('&sql_id', null, 'typical'));

@shared_cursor2 &sql_id

var b1 number;
exec :b1 := 1;
var b2 number;
exec :b2 := 1;

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;

@stat

-- optimizer mismatch
alter system flush shared_pool;
alter session set optimizer_index_cost_adj = 100;

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;

alter session set optimizer_index_cost_adj = 10;

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;

@shared_cursor2 &sql_id


-- translaction mismatch
@conn 1106
set timing off
alter system flush shared_pool;

select user from dual;

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;


connect ukja2/ukja2@ukja1021
set timing off

select user from dual;

drop table t1 purge;

create table t1(c1 int, c2 varchar2(10));

insert into t1 
select 1, level from dual connect by level <= 10000;

insert into t1
select level, level from dual connect by level <= 10000;

create index t1_n1 on t1(c1);

exec dbms_stats.gather_table_stats(user, 't1', -
		method_opt=>'for columns c1 size skewonly');

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;

@conn 1106
set timing off

@shared_cursor2 &sql_id


-- misc
alter session set optimizer_index_cost_adj = 100;

alter system flush shared_pool;

var b1 number;
exec :b1 := 1;
var b2 varchar2(10);
exec :b2 := '1';

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;

@stat

alter session set sql_trace = true;
var b1 number;
exec :b1 := 2;
var b2 varchar2(10);
exec :b2 := '1';

select /*+ gather_plan_statistics */ 
	count(*) from t1 
where c1 = :b1 and c2 = :b2;

@stat

alter session set sql_trace = false;

set line 120
select * from table(dbms_xplan.display_cursor('&sql_id', null, 'typical'));

@shared_cursor2 &sql_id


set timing on
set echo on
@capture_off

